﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using System.Text;
using Portal.Environment;
using Portal.Language;
using Portal.Interface;

namespace Portal.CDSP
{
    public class ContactBLL
    {
        public string InsertSuggest(SqlTransaction sqlTransaction, string Subject, string Content, string Email, string Name, string Address, string Phone, string belongSite)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string SuggestID = System.Guid.NewGuid().ToString();
            string Query = "INSERT INTO TableSuggestions" +
                                   "(SuggestID" +
                                   ",SuggestBy" +
                                   ",Subject" +
                                   ",Content" +
                                   ",Email" +
                                   ",Address" +
                                   ",Phone" +
                                   ",Time" +
                                   ",belongSite)  VALUES" +
                                   "(@SuggestID" +
                                   ",@SuggestBy" +
                                   ",@Subject" +
                                   ",@Content" +
                                   ",@Email" +
                                   ",@Address" +
                                   ",@Phone" +
                                   ",@Time" +
                                   ",@belongSite)";

            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@SuggestID", SqlDbType.VarChar);
            sqlCommand.Parameters["@SuggestID"].Value = SuggestID;

            sqlCommand.Parameters.Add("@Content", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Content"].Value = Content;

            // string belongSite = "vie";

            DateTime Time = DateTime.Now;
            sqlCommand.Parameters.Add("@Time", SqlDbType.DateTime);
            sqlCommand.Parameters["@Time"].Value = Time;

            sqlCommand.Parameters.Add("@SuggestBy", SqlDbType.NVarChar);
            sqlCommand.Parameters["@SuggestBy"].Value = Name;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            sqlCommand.Parameters.Add("@Email", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Email"].Value = Email;

            sqlCommand.Parameters.Add("@Address", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Address"].Value = Address;

            sqlCommand.Parameters.Add("@Phone", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Phone"].Value = Phone;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;
            try
            {
                sqlCommand.ExecuteNonQuery();
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
                return SuggestID;
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public DataTable SelectAll()
        {
            string Query = "select * from TableSuggestions order by time desc";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;

        }


        public ContactCls SelectByID(string SuggestID)
        {
            string Query = "select * from TableSuggestions where SuggestID=@SuggestID";

            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@SuggestID", SqlDbType.VarChar);
            sqlCommand.Parameters["@SuggestID"].Value = SuggestID;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                ContactCls FSGCls = new ContactCls();
                FSGCls.SuggestID = (string)dtResult.Rows[0]["SuggestID"];

                try
                {
                    FSGCls.SuggestBy = dtResult.Rows[0]["SuggestBy"].ToString();
                }
                catch { }

                try
                {
                    FSGCls.Subject = dtResult.Rows[0]["Subject"].ToString();
                }
                catch { }

                try
                {
                    FSGCls.Email = dtResult.Rows[0]["Email"].ToString();
                }
                catch { }

                try
                {
                    FSGCls.Address = dtResult.Rows[0]["Address"].ToString();
                }
                catch { }

                try
                {
                    FSGCls.Phone = dtResult.Rows[0]["Phone"].ToString();
                }
                catch { }

                try
                {
                    FSGCls.Time = (DateTime)dtResult.Rows[0]["Time"];
                }
                catch { }

                try
                {
                    FSGCls.Content = dtResult.Rows[0]["Content"].ToString();
                }
                catch { }
                return FSGCls;
            }
        }


        public void DeleteSuggest(SqlTransaction sqlTransaction, string SuggestID)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            // Environment.Permission.DeletePermission(sqlTransaction, AskID);
            string Query = "Delete from TableSuggestions where SuggestID=@SuggestID";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@SuggestID", SqlDbType.VarChar);
            sqlCommand.Parameters["@SuggestID"].Value = SuggestID;

            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }
    }
}
